package DAO.DAOImple;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import util.DBTool;
import model.Notice;
import model.Page;
import model.User;
import DAO.NoticeDAO;

/*
 * @author 崔鹏
 * @email cuismart@126.com
 */

public class NoticeImple implements NoticeDAO {

	public Notice addNotice(Notice notice,User user) {
	
		String sql = "INSERT INTO T_Notice (publishUser, publishTime, title, content) VALUES (?, ?, ?, ?)";
		
		
		PreparedStatement pstmt = null;
		
		DBTool dbt = DBTool.getDBTool();
		
		
			try {
				pstmt = dbt.getPstmt(sql);
				System.out.println(notice.getUser().getId());
				pstmt.setInt(1, notice.getUser().getId());
				pstmt.setString(2, (new SimpleDateFormat( "yyyy-MM-dd hh:mm").format(notice.getPublishTime())));
				pstmt.setString(3, notice.getTitle());
				pstmt.setString(4, notice.getContent());
				
				pstmt.executeUpdate();
				
			} catch (SQLException e) {
				e.printStackTrace();
			} finally {
				dbt.close(pstmt);
				dbt.closeConn();
			}
		
		
		return notice;
	}
	
	public void deleteNotice(int id) {
		
		String sql = "DELETE FROM T_Notice WHERE id = ?";

		PreparedStatement pstmt = null;
		
		DBTool dbt = DBTool.getDBTool();
		
		
			try {
				pstmt = dbt.getPstmt(sql);
				pstmt.setInt(1, id);
				
				pstmt.executeUpdate();
				
			} catch (SQLException e) {
				e.printStackTrace();
			} finally {
				dbt.close(pstmt);
				dbt.closeConn();
			}
		
	}

	public List<Notice> getNotice(Page page) {
		

//		String sql = "SELECT u.name,t.* FROM T_Notice as t,T_User as u where t.publishUser = u.id and t.state = 0";
		
		PreparedStatement pstmt = null;
		ResultSet rst = null;
		
		List<Notice> noticeList = new ArrayList<Notice>();
		
		DBTool dbt = DBTool.getDBTool();
		
		String sql = "SELECT u.name,n.* FROM T_Notice as n,T_User as u where n.publishUser = u.id and n.state = 0 order by n.id limit ?,?";
		try {
			pstmt = dbt.getPstmt(sql);
			pstmt.setInt(1, page.getStartRow());
			pstmt.setInt(2, page.getPageSize());
			rst = pstmt.executeQuery();

			while(rst.next()) {


				Notice notice = new Notice();
				
				notice.setId(rst.getInt("id"));
				User u = new User();
				u.setName(rst.getString("name"));
				notice.setPublishUser(u);
				notice.setPublishTime(rst.getTimestamp("publishTime"));
				notice.setTitle(rst.getString("title"));
//					notice.setContent(rst.getString("content"));
				notice.setContent(rst.getString("content"));
				notice.setState(rst.getInt("state"));
				
				noticeList.add(notice);
			}
			} catch (SQLException e) {
				e.printStackTrace();
			} finally {
				dbt.close(rst);
				dbt.close(pstmt);
				dbt.closeConn();
			}	
			
		return noticeList;
	}
	
	public Notice getNoticeInfo(int id) {
		

		String sql = "SELECT u.name,t.* FROM T_Notice as t,T_User as u where t.publishUser = u.id and t.id = ?";
		
		PreparedStatement pstmt = null;
		ResultSet rst = null;
		
		Notice notice = new Notice();
		List<Notice> noticeList = new ArrayList<Notice>();
		DBTool dbt = DBTool.getDBTool();
		
		try {
			pstmt = dbt.getPstmt(sql);
			System.out.println(id);
			pstmt.setInt(1, id);
			rst = pstmt.executeQuery();
			
			while(rst.next()) {
//				notice.setId(rst.getInt("id"));
				User u = new User();
				u.setName(rst.getString("name"));
				notice.setPublishUser(u);
				notice.setPublishTime(rst.getTimestamp("publishTime"));
				notice.setTitle(rst.getString("title"));
//					notice.setContent(rst.getString("content"));
				notice.setContent(rst.getString("content"));
System.out.println(rst.getString("content"));
				notice.setState(rst.getInt("state"));
				
//				noticeList.add(notice);
			}
			} catch (SQLException e) {
				e.printStackTrace();
			} finally {
				dbt.close(rst);
				dbt.close(pstmt);
				dbt.closeConn();
			}	
			
		return notice;
	}
	
	
	public void updateNotice(Notice notice) {
		
		String sql  = "UPDATE T_Notice SET publishTime = ?, title = ?, content = ? WHERE id = ?";
		
		PreparedStatement pstmt = null;
		
		DBTool dbt = DBTool.getDBTool();
		
		
		try {
			pstmt = dbt.getPstmt(sql);
			pstmt.setString(1, (new SimpleDateFormat( "yyyy-MM-dd hh:mm:ss").format(new Date())));
//			pstmt.setString(1, (new SimpleDateFormat("yyyy-MM-dd hh:mm:ss").format(new Date())));
			pstmt.setString(2, notice.getTitle());
			pstmt.setString(3, notice.getContent());
			pstmt.setInt(4, notice.getId());
			
			int result = pstmt.executeUpdate();
			if(result == 1) {
				System.out.println("success");
			} else {
				System.out.println();
			}
			
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			dbt.close(pstmt);
			dbt.closeConn();
		}
	}
	
 
	public Notice getLastNotice() {
		
		String sql = "SELECT u.name,t.* FROM T_Notice as t,T_User as u where t.publishUser = u.id";
		
		PreparedStatement pstmt = null;
		ResultSet rst = null;
		
		Notice notice = new Notice();
		
		DBTool dbt = DBTool.getDBTool();
		
		pstmt = dbt.getPstmt(sql);
		try {
			rst = pstmt.executeQuery();
			rst.beforeFirst();
			
			while(rst.next()) {
				notice.setId(rst.getInt("id"));
				User u = new User();
				u.setName(rst.getString("name"));
				notice.setPublishUser(u);
				notice.setPublishTime(rst.getTimestamp("publishTime"));
				notice.setTitle(rst.getString("title"));
				notice.setContent(rst.getString("content"));
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			dbt.close(rst);
			dbt.close(pstmt);
			dbt.closeConn();
		}
		
		return notice;
	}

	/*
	 * 查询总行数
	 */

	public int count() {
		
		int rowCount = 0;
		String sql = "select count(id) from T_Notice order by id asc";
		
		PreparedStatement pstmt = null;
		ResultSet rst = null;
		
		DBTool dbt = DBTool.getDBTool();
		pstmt = dbt.getPstmt(sql);
		
		try {
			rst = pstmt.executeQuery();
			rst.next();			//游标指向第一行
			rowCount = rst.getInt(1);	//取得总行数
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			dbt.close(rst);
			dbt.close(pstmt);
			dbt.closeConn();
		}
		
		return rowCount;
	}


	
}
